package GUI;

import com.CSV.Export;

import javax.naming.NoInitialContextException;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.table.JTableHeader;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.DoubleSummaryStatistics;


public class OracleGUI extends JFrame implements MouseListener, ItemListener {
    private JTabbedPane Base;
    // 定义选项卡上的嵌板
    /*
     * jp1,  录入学生个人信息
     * jp2,  修改考试科目及分值
     * jp3,  录入学生成绩信息
     * jp4,  删除学生成绩信息
     * jp5,  修改学生成绩信息
     * jp6   按系部，专业，班级汇总统计
     * jp7   查询不及格人数，最低分，最高分等
     * jp8   按照总成绩打印成绩单
     *  */
    private JPanel jp1, jp2, jp3, jp4, jp5, jp6,jp7,jp8;
// 定义各按钮
    /*
     * InsertStudent, 添加学生个人信息按钮
     * UpdateSubject, 增加考试科目及分值按钮
     * InsertClass, 录入学生成绩信息按钮
     * DeleteClass, 删除学生成绩信息按钮
     * UpdateClass, 修改学生成绩信息按钮
     * QueryDept,   按系部专业班级汇总
     * QueryMessage, 查询不及格人数，最低分，最高分等
     * QueryLow,
     * QueryHigh,
     * PrintfAllClass, 按照总成绩打印成绩单
     **/
    private JButton InsertStudent,UpdateSubject,InsertClass,DeleteClass,UpdateClass,QueryDept,
        QueryMessage,PrintfAllClass;
    // 定义各标签
    /*
     * InsertSno1,      插入学号提示标签
     * InsertName1,    插入姓名提示标签
     * InsertGender1,  插入性别提示标签
     * InsertDept1,    插入系别提示标签
     * InsertSpec1,    插入专业提示标签
     * InsertCla1,     插入班级提示标签
     * InsertAge1,     插入年龄提示标签
     * InsertPass1，    插入密码提示标签
     * UpdateCno1,      插入课程号提示标签
     * UpdateCla1,      插入科目提示标签
     * UpdatePoint1，   插入分值提示标签
     * InsertSn1,      插入添加成绩时的学号提示标签
     * InsertC1,       插入添加成绩时课程号提示表签
     * InsertP1,      插入添加成绩时分数提示标签
     * InsertS1,      插入删除成绩时的学号提示标签
     * InsertCn1,     插入删除成绩时的课程号提示标签
     * WillSno1,       插入你要修改学生的学号
     * WillCla1,       插入你要修改的科目标签
     * WillPoint1,      插入你要修改的分数标签
     * WillDept1,      插入系别标签
     * WillSpec1,      插入专业标签
     * WillCl1,      插入班级标签
     * PrintfCla1,     单科成绩打印的标签
     **/
    private JLabel InsertSno1,InsertName1,InsertGender1,InsertDept1,InsertSpec1,InsertCla1,InsertAge1,InsertPass1,UpdateCno1,
            UpdateCla1, UpdatePoint1,InsertSn1,InsertC1,InsertP1,InsertS1,InsertCn1,WillSno1,WillCla1,WillPoint1,WillDept1,WillSpec1,
            WillCl1;
    // 定义各文本框
    /*
     * InsertSno2,      插入学号文本框
     * InsertName2,    插入姓名文本框
     * InsertGender2,  插入性别文本框
     * InsertDept2,    插入系别文本框
     * InsertSpec2,    插入专业文本框
     * InsertCla2,     插入班级文本框
     * InsertAge2,     插入年龄文本框
     * InsertPass2,    插入密码文本框
     * UpdateCno2,     插入课程号提示标签
     * UpdateCla2,     插入科目文本框
     * UpdatePoint2，  插入分值文本框
     * InsertSn2,      插入添加成绩时的学号文本框
     * InsertC2,       插入添加成绩时课程号文本框
     * InsertP2,       插入添加成绩时分数文本框
     * InsertS2,          插入删除成绩时的学号文本框
     * InsertCn2,         插入删除成绩时的课程号文本框
     * WillSno2,          插入修改的学生的学号的文本框
     * WillCla2,          插入你要修改的文本框
     * WillPoint2         插入你要修改的文本框
     * WillDept2,      插入系别文本框
     * WillSpec2,      插入专业文本框
     * WillCl2,        插入班级文本框
     * PrintfCla2,        单科成绩打印的文本框
     * */
    private JTextField InsertSno2,InsertName2,InsertGender2,InsertDept2,InsertSpec2,InsertCla2,InsertAge2,InsertPass2,UpdateCno2,
            UpdateCla2, UpdatePoint2,InsertSn2,InsertC2,InsertP2,InsertS2,InsertCn2,WillSno2,WillCla2,WillPoint2,WillDept2,WillSpec2,
            WillCl2;
    //设置文本域
    private JTextArea Summary,Average,Max,Min;
    // 定义滚动条
    private JScrollPane scroll = null;
    private JScrollPane CourseScroll = null;

    private JScrollPane GradeScroll = null;


    OracleGUI() {
        // 设置各按钮信息
        setButton();
        // 设置各标签信息
        setLabel();
        // 设置各文本框信息
        setTextField();
        //设置文本域信息
        setTextArea();
        // 设置各面板信息
        setPanel();
        // 设置布局信息
        setLayout();
        // 设置选项卡信息
        setBase();
        // 设置主窗口信息
        setThis();

    }

    private void setButton(){
        // jp1 上的按钮
        InsertStudent = new JButton("添加");
        InsertStudent.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        InsertStudent.setBackground(Color.CYAN);
        InsertStudent.setBounds(300, 550, 100, 45);
        InsertStudent.setMargin(new Insets(0, 0, 0, 0));
        //jp2 上的按钮
        UpdateSubject = new JButton("修改");
        UpdateSubject.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        UpdateSubject.setBackground(Color.CYAN);
        UpdateSubject.setBounds(300, 550, 100, 45);
        UpdateSubject.setMargin(new Insets(0, 0, 0, 0));
        //jp3 上的按钮
        InsertClass = new JButton("录入");
        InsertClass.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        InsertClass.setBackground(Color.CYAN);
        InsertClass.setBounds(300, 550, 100, 45);
        InsertClass.setMargin(new Insets(0, 0, 0, 0));
        //jp4 上的按钮
        DeleteClass = new JButton("删除");
        DeleteClass.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        DeleteClass.setBackground(Color.CYAN);
        DeleteClass.setBounds(300, 550, 100, 45);
        DeleteClass.setMargin(new Insets(0, 0, 0, 0));
        //jp5 上的按钮
        UpdateClass = new JButton("修改");
        UpdateClass.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        UpdateClass.setBackground(Color.CYAN);
        UpdateClass.setBounds(300, 550, 100, 45);
        UpdateClass.setMargin(new Insets(0, 0, 0, 0));
        //jp6 上的按钮
        QueryDept = new JButton("系部汇总");
        QueryDept.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        QueryDept.setBackground(Color.CYAN);
        QueryDept.setBounds(300, 550, 100, 45);
        QueryDept.setMargin(new Insets(0, 0, 0, 0));
        //jp7 上的按钮
        QueryMessage = new JButton("查询信息：");
        QueryMessage.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        QueryMessage.setBackground(Color.CYAN);
        QueryMessage.setBounds(150, 40, 200, 45);
        QueryMessage.setMargin(new Insets(0, 0, 0, 0));
        //jp8 上的按钮
        PrintfAllClass = new JButton("打印");
        PrintfAllClass.setFont(new Font("宋体", 1, 20));      // 1 代表加粗，20 代表字体大小
        PrintfAllClass.setBackground(Color.CYAN);
        PrintfAllClass.setBounds(300, 550, 100, 45);
        PrintfAllClass.setMargin(new Insets(0, 0, 0, 0));

        initial();
    }

    private void setLabel(){
        // jp1 上的标签
        InsertSno1 = new JLabel("学    号：");
        InsertSno1.setFont(new Font("楷体", 1, 22));
        InsertSno1.setBackground(Color.GREEN);
        InsertSno1.setBounds(100, 40, 120, 50);
        InsertName1 = new JLabel("姓    名：");
        InsertName1.setFont(new Font("楷体", 1, 22));
        InsertName1.setBackground(Color.GREEN);
        InsertName1.setBounds(100, 100, 120, 50);
        InsertGender1 = new JLabel("姓    别：");
        InsertGender1.setFont(new Font("楷体", 1, 22));
        InsertGender1.setBackground(Color.GREEN);
        InsertGender1.setBounds(100, 160, 120, 50);
        InsertDept1 = new JLabel("系    别：");
        InsertDept1.setFont(new Font("楷体", 1, 22));
        InsertDept1.setBackground(Color.GREEN);
        InsertDept1.setBounds(100, 220, 120, 50);
        InsertSpec1 = new JLabel("专    业：");
        InsertSpec1.setFont(new Font("楷体", 1, 22));
        InsertSpec1.setBackground(Color.GREEN);
        InsertSpec1.setBounds(100, 280, 120, 50);
        InsertCla1 = new JLabel("班    级：");
        InsertCla1.setFont(new Font("楷体", 1, 22));
        InsertCla1.setBackground(Color.GREEN);
        InsertCla1.setBounds(100, 340, 120, 50);
        InsertAge1 = new JLabel("年    龄：");
        InsertAge1.setFont(new Font("楷体", 1, 22));
        InsertAge1.setBackground(Color.GREEN);
        InsertAge1.setBounds(100, 400, 120, 50);
        InsertPass1 = new JLabel("密    码：");
        InsertPass1.setFont(new Font("楷体", 1, 22));
        InsertPass1.setBackground(Color.GREEN);
        InsertPass1.setBounds(100, 460, 120, 50);
        // jp2 上的标签
        UpdateCno1 = new JLabel("课 程 号：");
        UpdateCno1.setFont(new Font("楷体", 1, 22));
        UpdateCno1.setBackground(Color.GREEN);
        UpdateCno1.setBounds(100, 160, 120, 50);
        UpdateCla1 = new JLabel("科    目：");
        UpdateCla1.setFont(new Font("楷体", 1, 22));
        UpdateCla1.setBackground(Color.GREEN);
        UpdateCla1.setBounds(100, 280, 120, 50);
        UpdatePoint1 = new JLabel("分    值：");
        UpdatePoint1.setFont(new Font("楷体", 1, 22));
        UpdatePoint1.setBackground(Color.GREEN);
        UpdatePoint1.setBounds(100, 400, 120, 50);
        // jp3 上的标签
        InsertSn1 = new JLabel("学    号：");
        InsertSn1.setFont(new Font("楷体", 1, 22));
        InsertSn1.setBackground(Color.GREEN);
        InsertSn1.setBounds(100, 40, 120, 50);
        InsertC1 = new JLabel("课 程 号：");
        InsertC1.setFont(new Font("楷体", 1, 22));
        InsertC1.setBackground(Color.GREEN);
        InsertC1.setBounds(100, 160, 120, 50);
        InsertP1 = new JLabel("分    数：");
        InsertP1.setFont(new Font("楷体", 1, 22));
        InsertP1.setBackground(Color.GREEN);
        InsertP1.setBounds(100, 280, 120, 50);
        // jp4中的标签
        InsertS1 = new JLabel("学    号：");
        InsertS1.setFont(new Font("楷体", 1, 22));
        InsertS1.setBackground(Color.GREEN);
        InsertS1.setBounds(100, 160, 120, 50);
        InsertCn1 = new JLabel("课 程 号：");
        InsertCn1.setFont(new Font("楷体", 1, 22));
        InsertCn1.setBackground(Color.GREEN);
        InsertCn1.setBounds(100, 280, 120, 50);
        // jp5中的标签
        WillSno1 = new JLabel("学    号：");
        WillSno1.setFont(new Font("楷体", 1, 22));
        WillSno1.setBackground(Color.GREEN);
        WillSno1.setBounds(100, 40, 120, 50);
        WillCla1 = new JLabel("课 程 号：");
        WillCla1.setFont(new Font("楷体", 1, 22));
        WillCla1.setBackground(Color.GREEN);
        WillCla1.setBounds(100, 160, 120, 50);
        WillPoint1 = new JLabel("修改分数：");
        WillPoint1.setFont(new Font("楷体", 1, 22));
        WillPoint1.setBackground(Color.GREEN);
        WillPoint1.setBounds(100, 280, 120, 50);
        // jp6标签
        WillDept1 = new JLabel("系    别：");
        WillDept1.setFont(new Font("楷体", 1, 22));
        WillDept1.setBackground(Color.GREEN);
        WillDept1.setBounds(100, 40, 120, 50);
        WillSpec1 = new JLabel("专    业：");
        WillSpec1.setFont(new Font("楷体", 1, 22));
        WillSpec1.setBackground(Color.GREEN);
        WillSpec1.setBounds(100, 160, 120, 50);
        WillCl1 = new JLabel("班    级：");
        WillCl1.setFont(new Font("楷体", 1, 22));
        WillCl1.setBackground(Color.GREEN);
        WillCl1.setBounds(100, 280, 120, 50);

    }

    // 设置各文本框信息的方法
    private void setTextField(){
        // jp1 上的文本框
        InsertSno2 = new JTextField();
        InsertSno2.setFont(new Font("宋体", 1, 23));
        InsertSno2.setBounds(210, 40, 200, 35);
        InsertName2 = new JTextField();
        InsertName2.setFont(new Font("宋体", 1, 23));
        InsertName2.setBounds(210, 100, 200, 35);
        InsertGender2 = new JTextField();
        InsertGender2.setFont(new Font("宋体", 1, 23));
        InsertGender2.setBounds(210, 160, 200, 35);
        InsertDept2 = new JTextField();
        InsertDept2.setFont(new Font("宋体", 1, 23));
        InsertDept2.setBounds(210, 220, 200, 35);
        InsertSpec2 = new JTextField();
        InsertSpec2.setFont(new Font("宋体", 1, 23));
        InsertSpec2.setBounds(210, 280, 200, 35);
        InsertCla2 = new JTextField();
        InsertCla2.setFont(new Font("宋体", 1, 23));
        InsertCla2.setBounds(210, 340, 200, 35);
        InsertAge2 = new JTextField();
        InsertAge2.setFont(new Font("宋体", 1, 23));
        InsertAge2.setBounds(210, 400, 200, 35);
        InsertPass2 = new JTextField();
        InsertPass2.setFont(new Font("宋体", 1, 23));
        InsertPass2.setBounds(210, 460, 200, 35);
        //jp2 上的文本框
        UpdateCno2 = new JTextField();
        UpdateCno2.setFont(new Font("宋体", 1, 23));
        UpdateCno2.setBounds(210, 160, 200, 35);
        UpdateCla2 = new JTextField();
        UpdateCla2.setFont(new Font("宋体", 1, 23));
        UpdateCla2.setBounds(210, 280, 200, 35);
        UpdatePoint2 = new JTextField();
        UpdatePoint2.setFont(new Font("宋体", 1, 23));
        UpdatePoint2.setBounds(210, 400, 200, 35);
        //jp3 上的文本框
        InsertSn2 = new JTextField();
        InsertSn2.setFont(new Font("宋体", 1, 23));
        InsertSn2.setBounds(210, 40, 200, 35);
        InsertC2 = new JTextField();
        InsertC2.setFont(new Font("宋体", 1, 23));
        InsertC2.setBounds(210, 160, 200, 35);
        InsertP2 = new JTextField();
        InsertP2.setFont(new Font("宋体", 1, 23));
        InsertP2.setBounds(210, 280, 200, 35);
        //jp4 上的文本框
        InsertS2 = new JTextField();
        InsertS2.setFont(new Font("宋体", 1, 23));
        InsertS2.setBounds(210, 160, 200, 35);
        InsertCn2 = new JTextField();
        InsertCn2.setFont(new Font("宋体", 1, 23));
        InsertCn2.setBounds(210, 280, 200, 35);
        //jp5 上的文本框
        WillSno2 = new JTextField();
        WillSno2.setFont(new Font("宋体", 1, 23));
        WillSno2.setBounds(210, 40, 200, 35);
        WillCla2 = new JTextField();
        WillCla2.setFont(new Font("宋体", 1, 23));
        WillCla2.setBounds(210, 160, 200, 35);
        WillPoint2 = new JTextField();
        WillPoint2.setFont(new Font("宋体", 1, 23));
        WillPoint2.setBounds(210, 280, 200, 35);
        //jp6文本框
        WillDept2 = new JTextField();
        WillDept2.setFont(new Font("宋体", 1, 23));
        WillDept2.setBounds(210, 40, 200, 35);
        WillSpec2 = new JTextField();
        WillSpec2.setFont(new Font("宋体", 1, 23));
        WillSpec2.setBounds(210, 160, 200, 35);
        WillCl2 = new JTextField();
        WillCl2.setFont(new Font("宋体", 1, 23));
        WillCl2.setBounds(210, 280, 200, 35);

    }

    private void setTextArea(){
        //jp6上的文本域
        Summary = new JTextArea();
        Summary.setFont(new Font("宋体", 1, 23));
        Summary.setBounds(500, 40, 400, 600);
        JScrollPane jslp = new JScrollPane(Summary);
        jslp.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
        //jp7上的文本域
        Average = new JTextArea();
        Average.setFont(new Font("宋体", 1, 23));
        Average.setBounds(500, 40, 400, 50);
        Max = new JTextArea();
        Max.setFont(new Font("宋体", 1, 23));
        Max.setBounds(500, 160, 400, 50);
        Min = new JTextArea();
        Min.setFont(new Font("宋体", 1, 23));
        Min.setBounds(500, 280, 400, 50);
    }

    // 设置各面板信息的方法
    private void setPanel() {
        jp1 = new JPanel();
        jp2 = new JPanel();
        jp3 = new JPanel();
        jp4 = new JPanel();
        jp5 = new JPanel();
        jp6 = new JPanel();
        jp7 = new JPanel();
        jp8 = new JPanel();
    }

    // 设置布局信息的方法
    private void setLayout(){
        // 添加 jp1 的组件
        jp1.setLayout(null);
        jp1.add(InsertStudent);
        jp1.add(InsertSno1);
        jp1.add(InsertName1);
        jp1.add(InsertGender1);
        jp1.add(InsertDept1);
        jp1.add(InsertSpec1);
        jp1.add(InsertCla1);
        jp1.add(InsertAge1);
        jp1.add(InsertPass1);
        jp1.add(InsertSno2);
        jp1.add(InsertName2);
        jp1.add(InsertGender2);
        jp1.add(InsertDept2);
        jp1.add(InsertSpec2);
        jp1.add(InsertCla2);
        jp1.add(InsertAge2);
        jp1.add(InsertPass2);
        //添加 jp2 的组件
        jp2.setLayout(null);
        jp2.add(UpdateSubject);
        jp2.add(UpdateCno1);
        jp2.add(UpdateCla1);
        jp2.add(UpdatePoint1);
        jp2.add(UpdateCno2);
        jp2.add(UpdateCla2);
        jp2.add(UpdatePoint2);
        //添加 jp3 的组件
        jp3.setLayout(null);
        jp3.add(InsertClass);
        jp3.add(InsertSn1);
        jp3.add(InsertC1);
        jp3.add(InsertP1);
        jp3.add(InsertSn2);
        jp3.add(InsertC2);
        jp3.add(InsertP2);
        //添加 jp4 的组件
        jp4.setLayout(null);
        jp4.add(DeleteClass);
        jp4.add(InsertS1);
        jp4.add(InsertCn1);
        jp4.add(InsertS2);
        jp4.add(InsertCn2);
        //添加 jp5的组件
        jp5.setLayout(null);
        jp5.add(UpdateClass);
        jp5.add(WillSno1);
        jp5.add(WillCla1);
        jp5.add(WillPoint1);
        jp5.add(WillSno2);
        jp5.add(WillCla2);
        jp5.add(WillPoint2);
        //添加 jp6的组件
        jp6.setLayout(null);
        jp6.add(QueryDept);
        jp6.add(WillDept1);
        jp6.add(WillSpec1);
        jp6.add(WillCl1);
        jp6.add(WillDept2);
        jp6.add(WillSpec2);
        jp6.add(WillCl2);
        jp6.add(Summary);
        //添加 jp7的组件
        jp7.setLayout(null);
        jp7.add(QueryMessage);
        jp7.add(Average);
        jp7.add(Max);
        jp7.add(Min);
        //添加 jp8的组件
        jp8.setLayout(null);
        jp8.add(PrintfAllClass);

    }

    // 设置选项卡信息的方法
    private void setBase() {
        Base = new JTabbedPane(JTabbedPane.TOP);
        Base.addTab("添加学生个人信息", jp1);
        Base.addTab("修改考试科目及分值", jp2);
        Base.addTab("录入学生成绩信息", jp3);
        Base.addTab("删除学生成绩信息", jp4);
        Base.addTab("修改学生成绩信息", jp5);
        Base.addTab("按系部，专业，班级汇总统计", jp6);
        Base.addTab("查询不及格人数，最低分，最高分等", jp7);
        Base.addTab("打印成绩单", jp8);
    }

    private void setThis() {
        this.add(Base);
        this.setTitle("学生信息发布/系统");
        this.setLocation(300, 200);
        this.setSize(1200, 800);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setResizable(false);
        this.setVisible(true);
    }

    void initial(){
        // 给各按钮添加监听器
        InsertStudent.addMouseListener(this);
        UpdateSubject.addMouseListener(this);
        InsertClass.addMouseListener(this);
        DeleteClass.addMouseListener(this);
        UpdateClass.addMouseListener(this);
        QueryDept.addMouseListener(this);
        QueryMessage.addMouseListener(this);
        PrintfAllClass.addMouseListener(this);
    }

    @Override
    public void itemStateChanged(ItemEvent e) {

    }

    @Override
    public void mouseClicked(MouseEvent e) {
        // 添加按钮功能
        if(e.getSource().equals(InsertStudent)){
            ONE();
        }
        else if(e.getSource().equals(UpdateSubject)){
            TWO();
        }
        else if(e.getSource().equals(InsertClass)){
            THREE();
        }
        else if(e.getSource().equals(DeleteClass)){
            FOUR();
        }
        else if(e.getSource().equals(UpdateClass)){
            FIVE();
        }
        else if(e.getSource().equals(QueryDept)){
            SIX();
        }
        else if(e.getSource().equals(QueryMessage)){
            SEVEN();
        }
        else if(e.getSource().equals(PrintfAllClass)){
            EIGHT();
        }

    }

    @Override
    public void mousePressed(MouseEvent e) {

    }

    @Override
    public void mouseReleased(MouseEvent e) {

    }

    @Override
    public void mouseEntered(MouseEvent e) {

    }

    @Override
    public void mouseExited(MouseEvent e) {

    }

    public void ONE(){
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc= new DataBaseConn();

        String sno = InsertSno2.getText();
        String name = InsertName2.getText();
        String gender = InsertGender2.getText();
        String dept = InsertDept2.getText();
        String spec = InsertSpec2.getText();
        String cla = InsertCla2.getText();
        String age = InsertAge2.getText();
        String pass = InsertPass2.getText();

        try {
            sql = "insert into s values('" + sno + "', '" + name + "', '" + gender + "', '" + dept + "', '" + spec + "','" + cla + "','" + age + "','" + pass + "')";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                JOptionPane.showMessageDialog(null, "已添加此学生的个人信息！", "添加成功！",JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void TWO(){
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc= new DataBaseConn();

        String cno = UpdateCno2.getText();
        String cname = UpdateCla2.getText();
        String credit = UpdatePoint2.getText();

        try {
            sql = "insert into c values('" + cno + "', '" + cname + "', '" + credit + "')";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                JOptionPane.showMessageDialog(null, "已增加课程相关信息！", "成功！",JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void THREE(){
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc= new DataBaseConn();

        String sno = InsertSn2.getText();
        String cno = InsertC2.getText();
        String grade = InsertP2.getText();


        try {
            sql = "insert into sc values('" + sno + "', '" + cno + "','" + grade + "')";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                JOptionPane.showMessageDialog(null, "已添加该学生此科成绩！", "添加成功！",JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void FOUR(){
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc= new DataBaseConn();

        String sno = InsertS2.getText();
        String cno = InsertCn2.getText();

        try {
            sql = "delete from sc where sno = '" + sno + "' and cno = '" + cno +"'";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                JOptionPane.showMessageDialog(null, "已删除该学生此科成绩！", "删除成功！",JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void FIVE(){
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc= new DataBaseConn();

        String sno = WillSno2.getText();
        String cno = WillCla2.getText();
        String grade = WillPoint2.getText();

        try {
            sql = "update sc set grade = '" + grade + "'where sno = '" + sno + "' and cno= '" + cno + "'";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                JOptionPane.showMessageDialog(null, "已修改该学生此科成绩！", "修改成功！",JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void SIX() {
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc = new DataBaseConn();

        String dept = WillDept2.getText();
        String spec = WillSpec2.getText();
        String cla = WillCl2.getText();

        try {
            sql = "select distinct * from sc where sno in(select sno from s where dept='" + dept + "'and spec = '" + spec + "'and cla = '" + cla + "')";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if(!rs.next()) {
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！", JOptionPane.ERROR_MESSAGE);
            }
            else{
                int count = 0;
                while (rs.next()) {
                    count++;
                }
                rs = ps.executeQuery();
                // 将查询获得的记录数据，转换成适合生成JTable的数据形式
                Object[][] info = new Object[count][3];
                count = 0;
                Summary.append("学生    科目    成绩\n\n");
                while (rs.next()) {
                    info[count][0] = rs.getString("sno");
                    info[count][1] = rs.getString("cno");
                    info[count][2] = rs.getString("grade");
                    String A = (String) info[count][0];
                    String B = (String) info[count][1];
                    String C = (String) info[count][2];
                    count++;
                    Summary.append(""+ A + "    " + B + "    " + C + "\n\n");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void SEVEN(){
        String sql1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc = new DataBaseConn();

        try {
            sql1 = "select count(sno) from sc where sno in(select distinct sno from sc where grade <60)";
            ps = dbc.getConn().prepareStatement(sql1);
            rs = ps.executeQuery();
            if (rs.next()){
                String ab = rs.getString("count(sno)");
                Average.append("不及格人数："+ab+"\n\n");
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }


            String sql2;
            PreparedStatement pst = null;
            ResultSet rst = null;
            DataBaseConn dbct = new DataBaseConn();
            try{
                sql2 = "select min(grade) from sc ";
                pst = dbct.getConn().prepareStatement(sql2);
                rst = pst.executeQuery();
                if (rst.next()){
                    String a = rst.getString("min(grade)");
                    Min.append("最低分为："+a+"\n\n");
                }else{
                    JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
                }


                String sql3;
                PreparedStatement p = null;
                ResultSet r = null;
                DataBaseConn db = new DataBaseConn();
                try {
                    sql3 = "select max(grade) from sc ";
                    p = db.getConn().prepareStatement(sql3);
                    r = p.executeQuery();
                    if (r.next()){
                        String b = r.getString("max(grade)");
                        Max.append("最高分为："+b+"\n\n");
                    }else{
                        JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
                    }
                }catch (SQLException e) {e.printStackTrace();}finally{
                    try {
                        p.close();
                        r.close();
                        DataBaseConn.connClose();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

            } catch (SQLException e) {e.printStackTrace();}finally{
                try {
                    pst.close();
                    rst.close();
                    DataBaseConn.connClose();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void EIGHT(){
        String sql;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DataBaseConn dbc= new DataBaseConn();

        try {
            sql = "select * from sc ";
            ps = dbc.getConn().prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){

                Export ex=new Export();
                ex.exportToCsv();

                JOptionPane.showMessageDialog(null, "已打印成EXCEL表格！", "打印成功！",JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(null, "可能是输入不规范！", "错误！",JOptionPane.ERROR_MESSAGE);
            }
        }catch (SQLException e) {e.printStackTrace();}finally{
            try {
                ps.close();
                rs.close();
                DataBaseConn.connClose();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}
